﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using SISCON.Model;
using log4net;
using SISCON.Common;

namespace SISCON.Dao
{
    public class DepartmentDAO : CommonDAO
    {
        /// <summary>
        /// Regresa todos los departamentos almacenados en la base de datos
        /// </summary>
        /// <returns></returns>
        public List<Department> GetAllDepartments()
        {
            SqlConnection conn = null;
            SqlDataReader reader = null;

            List<Department> lstDepartments = new List<Department>();

            try
            {
                conn = new SqlConnection(_ConnStr);
                conn.Open();

                string sql = " SELECT * FROM sis_department";
                SqlCommand sqlCommand = new SqlCommand(sql, conn);

                reader = sqlCommand.ExecuteReader();

                while (reader.Read())
                {
                    Department department = new Department();
                    department.DepartmentId = getReaderLong(reader, "department_id");
                    department.DepartmentCode = getReaderString(reader, "department_code");
                    department.DepartmentName = getReaderString(reader, "department_name");

                    lstDepartments.Add(department);
                }
                reader.Close();
            }
            catch (Exception exp)
            {
                throw exp;
            }
            finally
            {
                if (reader != null && !reader.IsClosed)
                {
                    reader.Close();
                }
                if (conn != null && conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
            }

            return lstDepartments;

        }

        /// <summary>
        /// Obtiene el departamento especificado por el departmentId
        /// </summary>
        /// <param name="departmentId">Id interno del departamento deseado</param>
        /// <returns>Departamento</returns>
        public Department GetDepartment(string departmentId)
        {
            SqlConnection conn = null;
            SqlDataReader reader = null;

            Department department = new Department();

            try
            {
                conn = new SqlConnection(_ConnStr);
                conn.Open();

                string sql = " SELECT * FROM sis_department WHERE department_id = @departmentId ";
                SqlCommand sqlCommand = new SqlCommand(sql, conn);
                sqlCommand.Parameters.Add("@departmentId", SqlDbType.BigInt);
                sqlCommand.Parameters["@departmentId"].Value = long.Parse(departmentId);
                reader = sqlCommand.ExecuteReader();

                while (reader.Read())
                {
                    department.DepartmentId = getReaderLong(reader, "department_id");
                    department.DepartmentCode = getReaderString(reader, "department_code");
                    department.DepartmentName = getReaderString(reader, "department_name");
                }
                reader.Close();
            }
            catch (Exception exp)
            {
                throw exp;
            }
            finally
            {
                if (reader != null && !reader.IsClosed)
                {
                    reader.Close();
                }
                if (conn != null && conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
            }

            return department;

        }

        /// <summary>
        /// Guarda un departamento dentro de la base de datos
        /// </summary>
        /// <param name="departmentId">Id interno del departamento</param>
        /// <param name="departmentCode">Codigo academico del departamento</param>
        /// <param name="departmentName">Nombre del departamento</param>
        public void SaveDepartment(string departmentId, string departmentCode, string departmentName)
        {
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection(_ConnStr);
                conn.Open();

                if (departmentId != "")
                {
                    string sqlQuery = "UPDATE sis_department SET department_name=@departmentName, department_code=@departmentCode " +
                        " WHERE department_id = @departmentId ";
                    SqlCommand sqlCommand = new SqlCommand(sqlQuery, conn);
                    sqlCommand.Parameters.Add("@departmentId", SqlDbType.BigInt);
                    sqlCommand.Parameters["@departmentId"].Value = long.Parse(departmentId);
                    sqlCommand.Parameters.Add("@departmentCode", SqlDbType.VarChar);
                    sqlCommand.Parameters["@departmentCode"].Value = departmentCode;
                    sqlCommand.Parameters.Add("@departmentName", SqlDbType.VarChar);
                    sqlCommand.Parameters["@departmentName"].Value = departmentName;

                    sqlCommand.ExecuteNonQuery();
                }
                else
                {
                    string sqlQuery = "INSERT INTO sis_department (department_name, department_code) VALUES " +
                        " ('@departmentName', @departmentCode)";
                    SqlCommand sqlCommand = new SqlCommand(sqlQuery, conn);
                    sqlCommand.Parameters.Add("@departmentCode", SqlDbType.VarChar);
                    sqlCommand.Parameters["@departmentCode"].Value = departmentCode;
                    sqlCommand.Parameters.Add("@departmentName", SqlDbType.VarChar);
                    sqlCommand.Parameters["@departmentName"].Value = departmentName;

                    sqlCommand.ExecuteNonQuery();
                }
            }
            catch (SqlException sqlEx)
            {
                if (sqlEx.Number == 2601)
                {
                    throw new SISCONException("El nombre de departamento ya existe. Favor de verificar");
                }
                else
                {
                    throw sqlEx;
                }
            }
            catch (Exception exp)
            {
                throw exp;
            }
            finally
            {
                if (conn != null && conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
            }
        }

        /// <summary>
        /// Elimina un departamento de la base de datos
        /// </summary>
        /// <param name="departmentId">Id interno del departamento que se desea eliminar</param>
        public void DeleteDepartment(string departmentId)
        {
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection(_ConnStr);
                conn.Open();

                string sql = "DELETE FROM sis_department WHERE department_id = @departmentId";
                SqlCommand sqlCommand = new SqlCommand(sql, conn);
                sqlCommand.Parameters.Add("@departmentId", SqlDbType.BigInt);
                sqlCommand.Parameters["@departmentId"].Value = long.Parse(departmentId);

                sqlCommand.ExecuteNonQuery();
            }
            catch (Exception exp)
            {
                throw exp;
            }
            finally
            {
                if (conn != null && conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
            }
        }

    }
}